//@Time:2022/4/2 16:06
//@Author:aFun

package indi.csms_springboot.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import indi.csms_springboot.domain.SCVO;
import indi.csms_springboot.domain.SSFOVO;
import indi.csms_springboot.domain.SSFSVO;
import indi.csms_springboot.domain.Student;
import org.apache.ibatis.annotations.*;

import java.util.Collection;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    @Select("SELECT c.couId,c.couName,ss.stuId,ss.score," +
            "MAX(ss.score) AS maxScore,MIN(ss.score) AS minScore,AVG(ss.score) AS avgScore " +
            "FROM stu2score ss JOIN course c ON ss.couId=c.couId " +
            "${ew.customSqlSegment}")
    public IPage<SSFSVO> studentScore(Page<SSFSVO> ssfsvoPage, @Param(Constants.WRAPPER) QueryWrapper<SSFSVO> wrapper);

    @Select("SELECT ss.couId " +
            "FROM stu2score ss " +
            "WHERE ss.stuId=${stuId}")
    public Collection<Integer> findCouIdByStuId(@Param("stuId")Integer stuId);

    @Select("SELECT h.stuRank FROM " +
            "(SELECT c.couId,c.couName,ss.stuId,ss.score,rank() over(ORDER BY ss.score DESC) AS stuRank " +
            "FROM stu2score ss JOIN course c ON ss.couId=c.couId " +
            "WHERE c.couId = ${couId}) h " +
            "WHERE h.stuId=${stuId}")
    public Integer setRank(@Param("couId")Integer couId,@Param("stuId")Integer stuId);

    // 通过account找到stuId
    @Select("SELECT stuId from user u JOIN student s ON u.account=s.account WHERE u.account='${account}'")
    public Integer findStuIdByAccount(@Param("account")String account);


    // 查询学生的课程表
    @Select("SELECT ss.ssId,s.stuId,u.name AS stuName,c.couId,c.couName,ss.teaId," +
            "(SELECT u2.name FROM USER u2 WHERE account = (SELECT account FROM teacher t WHERE t.teaId=ss.teaId)) AS teaName " +
            "FROM USER u JOIN student s JOIN course c JOIN stu2score ss " +
            "ON u.account=s.account AND s.stuId=ss.stuId AND c.couId=ss.couId " +
            "${ew.customSqlSegment}")
    public IPage<SCVO> findSC(Page<SCVO> ssfsvoPage, @Param(Constants.WRAPPER) QueryWrapper<SCVO> wrapper);

    // 退课
    @Delete("delete from stu2score where ssId=${ssId}")
    public boolean deleteCourse(@Param("ssId")Integer ssId);

    // 展示可选课程
    @Select("SELECT c.couId,c.couName,t.teaId,u.name AS teaName " +
            "FROM course c JOIN tea2cou tc JOIN USER u JOIN teacher t " +
            "ON c.couId=tc.couId AND tc.teaId=t.teaId AND t.account=u.account " +
            "${ew.customSqlSegment}")
    public IPage<SCVO> findSCToSelect(Page<SCVO> ssfsvoPage, @Param(Constants.WRAPPER) QueryWrapper<SCVO> wrapper);


    // 选课
    @Insert("INSERT INTO stu2score VALUES(NULL,${stuId},${couId},0,${teaId})")
    public boolean selectCourse(@Param("stuId")Integer stuId,@Param("couId")Integer couId,@Param("teaId")Integer teaId);
}
